﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using KPIS.GERP.GAMS.DAL;
using OfficeOpenXml;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class _ImportTransectionA : System.Web.UI.Page
    {
        public string FileAccountCodeMapOldCodePath;
        public string FileรายรับPath; 
        public D_ACM_GENERAL_Ledger dal = new D_ACM_GENERAL_Ledger();
        public const string space = @"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";

        protected void Page_Load(object sender, EventArgs e)
        {
            FileAccountCodeMapOldCodePath = txtFileMapPath.Text;
            FileรายรับPath = txtIncomePath.Text; 
        }

        protected void lbl1_Click(object sender, EventArgs e)
        {
            Write(false);
        }

        protected void lbl2_Click(object sender, EventArgs e)
        {
            Write(true);
        }

        public void Write(bool writeDb)
        {
            string messageResult = "";
            messageResult += WriteEach(writeDb, FileรายรับPath); 
            ltrResult.Text = messageResult + "<hr/><br/><br/>Db query Log<br/>" + dal.StringDebug;
        }

        public string WriteEach(bool writeDb, string filePath)
        {
            string messageResult ; 
                int transectionCount;
                int transectionDtlCount;
                var arrResult1 = WriteTransaction(filePath, out transectionCount, out transectionDtlCount, writeDb);
                string m1 =  string.Join("<br/>", (from a in arrResult1
                                                                        select a).ToArray()) + "<br/><br/><hr/>";
                
                int amountRecordUseable = arrResult1.Count(o => o.Contains("Useable:ใช้"));
                int amountRecordUnuseable = arrResult1.Count(o => o.Contains("Useable:ไม่ใช้"));
                int amountAccountSeqNull = arrResult1.Count(o => o.Contains("account_seq:(null)"));
                messageResult = string.Format("<h1>Excel ไฟล์พาท:{0}, จำนวนแถวตั้งแต่บรรทัดที่2ขึ้นไป: {1}, มีค่าตัวเลข: {2}, ไม่มีค่าตัวเลข: {3}, " +
                                              "จำนวน Dtl ที่ AccountSeq เป็น null: {4}, จำนวน transection {6}, จำนวน transection_dtls {7}</h1>{5}<hr/><br/><br/>",
                                              filePath,
                                              arrResult1.Count,
                                              amountRecordUseable,
                                              amountRecordUnuseable,
                                              amountAccountSeqNull,
                                              m1,
                                              transectionCount,
                                              transectionDtlCount);
            
            return messageResult;
        }

        public List<string> WriteTransaction(string fileTransactionPath, out int transectionCount, out int transectionDtlCount, bool writeDb = true)
        {
            Dictionary<string, int?> lstAccountSeqMapOldCode = LoadAccountSeqMapOldColde();
            Dictionary<string, int?> lstAccountSeqMapNewCode = LoadAccountSeqMapNewAccountCode();
            int lastRowNum;
            ImportModel temp = null;
            var debug = new List<string>();
            var ims = new List<ImportModel>();
            var t = new List<acm_transection>();
            transectionCount = 0;
            transectionDtlCount = 0;

            using (ExcelPackage pck = new ExcelPackage(new FileInfo(fileTransactionPath), true))
            { 
                var ws1 = pck.Workbook.Worksheets["Sheet1"];

                lastRowNum = ws1.Dimension.End.Row; 

                //Normalizing and collect data to model array
                for (int i = 2; i <= lastRowNum; i++)
                {
                    var im = new ImportModel();
                    im.ExcelRow = i;
                    im.A_PQ = Convert.ToString(ws1.Cells["A" + i].Value);
                    im.B_DateOfTransection = ConvertDateTimeNull(ws1.Cells["B" + i].Text);
                    im.C_Details = Convert.ToString(ws1.Cells["C" + i].Value).Trim();
                    im.D_NewCode = Convert.ToString(ws1.Cells["D" + i].Value).Trim();
                    im.E_OldCode = Convert.ToString(ws1.Cells["E" + i].Value).Trim();
                    im.F_Dr = ConvertDoubleNull(ws1.Cells["F" + i].Text.Trim());
                    im.G_Cr = ConvertDoubleNull(ws1.Cells["G" + i].Text.Trim());

                    if(string.IsNullOrEmpty(im.A_PQ))
                    {
                        for(int j = ims.Count -1 ; j >= 0 ; j--) 
                        {
                            if(!string.IsNullOrEmpty(ims[j].A_PQ))
                            {
                                im.A_PQ = ims[j].A_PQ;
                                im.B_DateOfTransection = ims[j].B_DateOfTransection;
                                im.C_Details = ims[j].C_Details;
                                break;
                            }
                        } 
                    }
                    ims.Add(im);
                    
                    if ((temp == null || im.A_PQ != temp.A_PQ))
                    {
                        temp = im;
                    } 
                }

                // Collect transection
                List<string> pqAdded = new List<string>();
                acm_transection temp2 = null;
                foreach(ImportModel im in ims)
                {
                    if(!pqAdded.Exists(o=>o == im.A_PQ))
                    {
                        pqAdded.Add(im.A_PQ);
                        acm_transection tItem = new acm_transection();
                        tItem.RefCode = im.A_PQ;
                        tItem.tran_type = 'A';
                        tItem.fiscal_period = "2013/10";
                        tItem.tran_grp_seq = null; 
                        tItem.tran_date = im.B_DateOfTransection.Value;
                        tItem.bgm_year = 2557;
                        tItem.work_area_seq = null;
                        tItem.bgm_source_seq = 11;
                        tItem.ref_no = null;
                        tItem.tran_name = string.Format("จ่ายค่าใช้จ่ายค้างจ่าย {0}", im.A_PQ);
                        tItem.remarks = "Generated";
                        tItem.record_status = "W";
                        tItem.created_by = 1;
                        tItem.created_when = DateTime.Now;
                        tItem.ExcelRow = im.ExcelRow;
                        temp2 = tItem;
                        transectionCount++;
                        t.Add(tItem);
                    }
                    im.acm_transection = temp2;
                }

                // Collect transection_dtls
                foreach(ImportModel im in ims)
                {
                    if(im.Useable)
                    {
                        acm_transection_dtls tItemDtl = new acm_transection_dtls();
                        tItemDtl.tran_seq = 0; 
                        if(!string.IsNullOrEmpty(im.E_OldCode))
                        {
                            tItemDtl.useCodeCode = im.E_OldCode;
                            tItemDtl.useCodeType = "รหัสเก่า";
                            tItemDtl.account_seq = lstAccountSeqMapOldCode.FirstOrDefault(o => o.Key == im.E_OldCode).Value;
                        } else
                        {
                            tItemDtl.useCodeCode = im.D_NewCode;
                            tItemDtl.useCodeType = "รหัสใหม่";
                            tItemDtl.account_seq = lstAccountSeqMapNewCode.FirstOrDefault(o => o.Key == im.D_NewCode).Value;
                        } 
                        tItemDtl.dr_or_cr = im.dr_or_cr;
                        tItemDtl.amount = im.amount;
                        tItemDtl.record_status = "W";
                        tItemDtl.created_by = 1;
                        tItemDtl.created_when = DateTime.Now;
                        tItemDtl.ExcelRow = im.ExcelRow;
                        im.acm_transection_dtls = tItemDtl;
                        transectionDtlCount++;
                        t.First(o=>o.RefCode == im.A_PQ).AcmTransectionDtlses.Add(tItemDtl);
                    }
                }

                // Write transaction to db
                foreach(var tItem in t)
                {
                    var tran_seq = InsertTransaction(tItem);
                    tItem.tran_seq = tran_seq;
                    foreach(var tItemDtls in tItem.AcmTransectionDtlses)
                    {
                        tItemDtls.tran_seq = tran_seq;
                        InsertTransactionDtl(tItemDtls);
                    }
                } 

                foreach(var im in ims)
                {
                    debug.Add("Row#" + im.ExcelRow + "<br/>" + 
                        im + "<br/>" +
                        im.acm_transection + "<br/>"+ 
                        im.acm_transection_dtls + "<br/>");
                }
            }
            return debug;
        }


        #region Misc
        public Dictionary<string, int?> LoadAccountSeqMapNewAccountCode()
        {
            var result = new Dictionary<string, int?>();
            foreach(DataRow dr in dal.Execute("select account_seq, account_code from acm_mas_account where record_status != 'D'").Rows)
            {
                string accountCode = Convert.ToString(dr["account_code"]);
                int? accountSeq = Convert.ToInt32(dr["account_seq"]);
                if(!string.IsNullOrEmpty(accountCode) && result.All(o=>o.Key != accountCode))
                {
                    result.Add(Convert.ToString(dr["account_code"]), Convert.ToInt32(dr["account_seq"]));
                }
            }
            return result;
        }
        public int InsertTransaction(acm_transection a )
        { 
            return dal.ExecuteNonQuery(@"INSERT INTO [dbo].[acm_transection] ([tran_type], [fiscal_period], [tran_grp_seq]           
                            ,[tran_code], [tran_date], [bgm_year], [work_area_seq], [bgm_source_seq], [ref_no], [tran_name]  
                            ,[remarks] ,[record_status], [created_by], [created_when] ) VALUES (@tran_type, @fiscal_period           
                            ,@tran_grp_seq , @tran_code , @tran_date , @bgm_year , @work_area_seq , @bgm_source_seq , @ref_no 
                            , @tran_name , @remarks , @record_status , @created_by , @created_when  );

                            SELECT SCOPE_IDENTITY();
                            ", new Dictionary<string, object>
                            {
                                {"tran_type", a.tran_type},{"fiscal_period", a.fiscal_period},{"tran_grp_seq", a.tran_grp_seq},
                                {"tran_code", a.tran_code},{"tran_date", a.tran_date},{"bgm_year", a.bgm_year},
                                {"work_area_seq", a.work_area_seq},{"bgm_source_seq", a.bgm_source_seq},{"ref_no", a.ref_no},
                                {"tran_name", a.tran_name},{"remarks", a.remarks},{"record_status", a.record_status},
                                {"created_by", a.created_by},{"created_when", a.created_when}
                            });
        }

        public void InsertTransactionDtl(acm_transection_dtls d)
        {
            dal.ExecuteNonQuery(@"INSERT INTO [dbo].[acm_transection_dtls] ([tran_seq], [account_seq], [dr_or_cr]
                        , [amount], [record_status], [created_by], [created_when] ) VALUES           (@tran_seq, @account_seq, @dr_or_cr, @amount
                        , @record_status, @created_by, @created_when )
                        ", new Dictionary<string, object>
                            {
                                {"tran_seq", d.tran_seq},{"account_seq", d.account_seq},{"dr_or_cr", d.dr_or_cr},{"amount", d.amount},
                                {"record_status", d.record_status},{"created_by", d.created_by},{"created_when", d.created_when}});
        }

        public Dictionary<string, int?> LoadAccountSeqMapOldColde()
        {
            // LoadAccountSeqMapOldColde from excel ColumnA = "AccountCode", ColumB = "OldCode"
            Dictionary<string, int?> lstAccountSeqMapOldCode = new Dictionary<string, int?>();
            using (ExcelPackage pck = new ExcelPackage(new FileInfo(FileAccountCodeMapOldCodePath), true))
            {
                var ws1 = pck.Workbook.Worksheets["Sheet1"];
                var lastestRow = ws1.Dimension.End.Row;
                for (int i = 2; i <= lastestRow; i++)
                {
                    int? account_seq = ConvertIntNull(ws1.Cells["A" + i].Value);
                    string oldCode = Convert.ToString(ws1.Cells["B" + i].Value);
                    if(!string.IsNullOrEmpty(oldCode) && lstAccountSeqMapOldCode.All(o => o.Key != oldCode))
                    { 
                        lstAccountSeqMapOldCode.Add(oldCode, account_seq);
                    }
                }
            }
            return lstAccountSeqMapOldCode;
        }

        public int? ConvertIntNull(object obj)
        {
            int? result = null;
            int temp;
            string objStr = (obj ?? "").ToString();
            if(Int32.TryParse(objStr, out temp))
            {
                result = temp;
            }
            return result;
        }
        
        public double? ConvertDoubleNull(object obj)
        {
            double? result = null;
            double temp;
            string objStr = (obj ?? "").ToString();
            if(Double.TryParse(objStr, out temp))
            {
                result = temp;
            }
            return result;
        }

        public DateTime? ConvertDateTimeNull(object obj)
        {
            DateTime? result = null;
            DateTime temp;
            string objStr = (obj ?? "").ToString();
            if(DateTime.TryParse(objStr, out temp))
            {
                result = temp;
            }
            return result;
        }
        #endregion
    }

    #region Model
    public class ImportModel
    {
        public int ExcelRow { get; set; }
        public string A_PQ { get; set; }
        public DateTime? B_DateOfTransection { get; set; }
        public string C_Details { get; set; }
        public string D_NewCode { get; set; }
        public string E_OldCode { get; set; }
        public double? F_Dr { get; set; }
        public double? G_Cr { get; set; }

        public int tran_seq { get; set; }
        public bool HasData { get { return !string.IsNullOrEmpty(E_OldCode) || !string.IsNullOrEmpty(D_NewCode); } }
        public bool Useable{get { return HasData && amount.HasValue; }}
        public string dr_or_cr{get { return F_Dr.HasValue ? "Dr" : "Cr"; }}
        public double? amount{get { return dr_or_cr == "Dr" ? F_Dr : G_Cr; }}

        public override string ToString()
        {
            return string.Format(@"<span style='color:{8}'>{{[ImportModel has these fields / Useable:{7} | A_PQ:{0}  |  B_DateOfTransection:{1}
C_Details:{2}  |  E_OldCode:{3}  |  D_NewCode:{4}  |  dr_or_cr:{5}  |  amount:{6}}}</span>",
                                 A_PQ,
                                 B_DateOfTransection.HasValue ? B_DateOfTransection.Value.ToString("d/MM/yy") : "(null)",
                                 C_Details,
                                 string.IsNullOrEmpty(E_OldCode) ? "(ไม่มีรหัสเก่า)" : E_OldCode,
                                 string.IsNullOrEmpty(D_NewCode) ? "(ไม่มีรหัสใหม่)" : D_NewCode,
                                 dr_or_cr,
                                 amount.HasValue ? amount.Value.ToString("#,##0.00") : "(null)",
                                 Useable ? "ใช้":"ไม่ใช้",
                                 Useable ? "Green":"Orange");
        }
        
        public acm_transection acm_transection { get; set; }
        public acm_transection_dtls acm_transection_dtls { get; set; }
    }

    public class acm_transection
    {
        public acm_transection()
        {
            AcmTransectionDtlses = new List<acm_transection_dtls>();
        }
        public string RefCode { get; set; }
        public int tran_seq { get; set; }
        public char tran_type { get; set; }
        public string fiscal_period { get; set; }
        public int? tran_grp_seq { get; set; }

        public string tran_code
        {
            get
            {
                return string.IsNullOrEmpty(tran_code_) ?
                    new D_ACM_GENERAL_Ledger().GetNextTranCode(Convert.ToString(bgm_source_seq), Convert.ToString(bgm_year), Convert.ToString(tran_type))
                           : tran_code_;
            }
            set { tran_code_ = value; }
        }

        private string tran_code_ { get; set; }
        public DateTime tran_date { get; set; }
        public int? bgm_year { get; set; } 
        public int? work_area_seq { get; set; } 
        public int? bgm_source_seq { get; set; } 
        public string ref_no { get; set; } 
        public string tran_name { get; set; } 
        public string remarks { get; set; } 
        public string record_status { get; set; } 
        public int? created_by { get; set; } 
        public DateTime? created_when { get; set; } 
        public int? updated_by { get; set; } 
        public DateTime? updated_when { get; set; } 
        public int? approved_by { get; set; } 
        public DateTime? approved_when { get; set; }
        public int ExcelRow { get; set; }
        public override string ToString()
        {
            return string.Format("{{acm_transection has these fields / RefCode:{0}  |  tran_seq:{1}  |  tran_type:{2}  |  fiscal_period:{3}  |  tran_grp_seq:{4}  |  tran_code:{5}" +
                                 "  |  tran_date:{6}  |  bgm_year:{7}  |  work_area_seq:{8}  |  bgm_source_seq:{9}  |  ref_no:{10}" +
                                 "  |  tran_name:{11}  |  remarks:{12}  |  record_status:{13}  |  created_by:{14}  |  created_when:{15}  |  ExcelRow:{16}}}",
                                 RefCode, tran_seq, tran_type, fiscal_period, tran_grp_seq, tran_code,
                                 tran_date.ToString("d/MM/yyyy"), bgm_year,work_area_seq,bgm_source_seq,ref_no,
                                 tran_name, remarks,record_status,created_by,created_when,ExcelRow);
        }
        public List<acm_transection_dtls> AcmTransectionDtlses { get; set; } 
    }

    public class acm_transection_dtls
    {
        public int tran_dtls_seq { get; set; }
        public int? tran_seq { get; set; }
        public int? account_seq { get; set; }
        public string dr_or_cr { get; set; }
        public double? amount { get; set; }
        public string record_status { get; set; } 
        public int? created_by { get; set; } 
        public DateTime? created_when { get; set; } 
        public int? updated_by { get; set; } 
        public DateTime? updated_when { get; set; }
        public string useCodeCode { get; set; }
        public string useCodeType { get; set; } 
        public int ExcelRow { get; set; }
        public override string ToString()
        {
            return string.Format("{{acm_transection_dtls has these fields / tran_dtls_seq:{0}  |  tran_seq:{1}  |  <span style='background-color:{12}'>account_seq:{2}</span>  |  dr_or_cr:{3}  |  amount:{4}" +
                                 "  |  record_status:{5}  |  created_by:{6}  |  created_when:{7}  |  useCodeCode:{8}  |  useCodeType:{9}  |  useCodeType:{10}  |  ExcelRow:{11}}}",
                                 tran_dtls_seq, tran_seq, account_seq.HasValue?account_seq.Value.ToString():"(null)", dr_or_cr, amount,
                                 record_status, created_by,created_when,useCodeCode,useCodeType,useCodeType,ExcelRow,
                                 account_seq.HasValue?"White":"Red");
        }
    }
    #endregion

}